							SELECT 
							 S.PROPERTY_ID 
							,S.ADDRESS
							--,UNIT
							,SC.REGION
							,SC.PROPERTY_NAME
							,SC.NATIONAL_IND
							,SC.DWELLING_TYPE
							,SC.AM
							,SC.AE_ASSIGNED
							,SC.PORTFOLIO_NAME
							--,SC.PR_STATUS
							--,SC.C_STATUS
							,SC.MARKET_AREA
							--,' ' AS DISTRIBUTION_AREA
							--,E.RAAID
							--,E.EVENT_ID
							,E.SONBR
							--,DISC_RSN_CD_U
							--,DISC_RSN_U   
							,SALES_CODE
							--,SALES_CHANNEL_CD
							--,BAN
							,ORDER_TYPE
							,SOURCE_CD
							,E.POSTDATE
							,E.CONTACT_NM
							,E.CITY_NM
							,E.STATE
							,E.ZIP_CD
							FROM 
							SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
							S
							,SMARTMOVES.TXSM101_SO_SCORE 	
							SC
							,(	-- E		  		
					  			SELECT 
					  			 RAAID
								,EVENT_ID
								,SONBR
								--,DISC_RSN_CD_U
								--,DISC_RSN_U   
								,SALES_CODE
								--,SALES_CHANNEL_CD
								--,BAN
								,ORDER_TYPE
								,SOURCE_CD
								,POSTDATE
								,CONTACT_NM
								,CITY_NM
								,ADDRESS
								,UNIT
								,STATE
								,ZIP_CD					
								FROM
								( -- BASE
									SELECT	
									 REPOSITORY_AFFILIATE_ACCT_ID RAAID
									,EVENT_ID
									,SONBR
									--,DISC_RSN_CD_U
									--,DISC_RSN_U   
									,SALES_CODE
									--,SALES_CHANNEL_CD
									--,BAN
									,ORDER_TYPE
									,SOURCE_CD
									,POSTDATE
									,ADDRESS_ID
									,CONTACT_NM
									,CITY_NM
									,PRIMARY_ADDRESS_TXT
									,SECONDARY_ADDRESS_TXT AS  UNIT
									,TRIM(C.SECONDARY_ADDRESS_TXT) (NAMED UNIT_ADD)     
							         ,CASE WHEN C.STREET_DIRECTION_PREFIX_TXT > ' '      
							             THEN TRIM(C.STREET_DIRECTION_PREFIX_TXT) ||' '||
							                  TRIM(C.STREET_NM)                          
							             ELSE C.STREET_NM                                
							             END (NAMED ADR1)                                
							         ,CASE WHEN C.ADDRESS_HOUSE_NBR > ' '                
							             THEN TRIM(C.ADDRESS_HOUSE_NBR) ||' '|| TRIM(ADR1)    
							             ELSE ADR1                                            
							             END (NAMED ADR2)                                     
							         ,CASE WHEN C.ADDRESS_SUFFIX_TYPE_CD > ' '                
							             THEN TRIM(ADR2) ||' '|| TRIM(C.ADDRESS_SUFFIX_TYPE_CD)
							             ELSE ADR2                                            
							             END (NAMED ADR3)                                     
							         ,CASE WHEN C.STREET_DIRECTION_SUFFIX_TXT > ' '           
							             THEN TRIM(ADR3) ||' '||                              
							                  TRIM(C.STREET_DIRECTION_SUFFIX_TXT)             
							             ELSE ADR3                                            
							             END (NAMED ADDRESS)      
									,C.ZIP_CD
									,C.TERRITORY_CD AS STATE
								    FROM  
								 	ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
									C  
									,
									( --S
										SELECT 
			  							 H205.REPOSITORY_AFFILIATE_ACCT_ID  RAAID  
								 		,H205.EVENT_ID
										,H205.SERVICE_ORDER_NBR        		SONBR
										--,DISC_RSN_CD_U
										--,DISC_RSN_U     
										--,O.BAN
										,H205.EVENT_POSTED_DT    			POSTDATE
										,H205.EVENT_COMPLETION_DT    		CMPLDATE 
										,H205.EVENT_CLASS_CD				ORDER_TYPE
										,H205.DATA_SOURCE_CD				SOURCE_CD
										,SUBSTR(H205.ORIGINATING_SALES_CD,1,7) AS SALES_CODE     
										--,CASE WHEN O.SALES_CHANNEL_CD   = 'WEB' THEN 'ONLINE' ELSE O.SALES_CHANNEL_CD END AS SALES_CHANNEL_CD            
										FROM
										 ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT H205
										 ,( --O
										 		SELECT 
										 		 O.ORDER_ID	
												,O.SERVICE_ORDER_CD
												--,O.BAN
												--,SALES_CHANNEL_CD
												--,DISC_RSN_CD_U     
												--,DISC_RSN_U
											  	FROM	  TELCO_UNREG_RETAIL_VIEWS.VCTV800_ORDER	
											  	O					
												LEFT OUTER JOIN 
												( --D
													SELECT
													 A.ORDER_ID
													,A.ORDER_ACTION_TYPE_CD
													,A.ORDER_ACTION_REASON_CD   DISC_RSN_CD_U
													,C.ORDER_ACTION_REASON_DESC DISC_RSN_U
													FROM
													TELCO_UNREG_RETAIL_VIEWS.VCTV801_ORDER_ACTION  	 
													A 
													INNER JOIN TELCO_UNREG_RETAIL_VIEWS.VCTV804_ORDER_ACTION_RSN_DESC  
													C
													ON A.ORDER_ACTION_REASON_CD = C.ORDER_ACTION_REASON_CD
													AND	A.ORDER_ACTION_TYPE_CD = C.ORDER_ACTION_TYPE_CD
													AND A.ORDER_ACTION_REVISION_TYPE_CD = C.ORDER_ACTION_REVISION_TYPE_CD
													WHERE  												
													A.CURRENT_ORDER_ACTION_STATUS_CD = 'DO'
													AND A.ORDER_ACTION_TYPE_CD IN ('CH', 'CE') 
											)
											D
										   	ON O.ORDER_ID = D.ORDER_ID
										)
										O	---------------------------------------------------------------------------------------------------------------------------------------------------------------												
										WHERE   
										 H205.EVENT_POSTED_DT >= DATE '2009-12-01' AND
									     H205.DATA_SOURCE_CD IN (150) AND
										 O.ORDER_ID = SONBR AND
										 RAAID > 0
									) 
									S
									WHERE C.REPOSITORY_AFFILIATE_ACCT_ID = S.RAAID
			                        AND C.ZIP_CD <> ' '
									AND C.ADDRESS_USAGE_CD = 'S' 
								) 
								BASE
								--GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
							) 
							E
							WHERE TRIM(S.ADDRESS) = TRIM(E.ADDRESS)
							AND S.ZIP = E.ZIP_CD
							AND S.PROPERTY_ID = SC.PROPERTY_ID
							AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' ) 